package com.cinema.dao.imp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cinema.Bean.Hall;
import com.cinema.Bean.MovieSession;
import com.cinema.Bean.MsgStatistical;
import com.cinema.Bean.Ticket;
import com.cinema.dao.BaseDao;
import com.cinema.dao.ITicketDao;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.cinema.util.*;
/**
 * 影票表的mysql数据库实现类
 * **/

public class TicketDaoMysql extends BaseDao implements ITicketDao {
	public int insert(Ticket a) {
		String sql = "insert into ticket(userId,sessionId,seatRow,seatCol,hallId,movieId) values(?,?,?,?,?,?)";
		String[] args = { a.getUserId(),a.getSessionId(),a.getSeatRow()+"",a.getSeatCol()+"",a.getHallId(),a.getMovieId()};
		return insertReturnId(sql, args);
	}
	/**
	 * 返回自增主键
	 * @param sql
	 * @param args
	 * @return
	 */
	protected int insertReturnId(String sql, String[] args) {
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < args.length; i++) {
				pstmt.setString(i + 1, args[i]);
			}
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();    
			int id=0;
	            if (rs.next()) {    
	                id = rs.getInt(1);    
	            }  
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public int delete(Ticket a) {
		String sql = "delete from ticket where ticketId=?";
		String[] args = { a.getTicketId()+"" };
		return change(sql, args);
	}

	public int update(Ticket a) {
		String sql = "update ticket set userId=?, sessionId=?,seatRow=?,seatCol=? where ticketId=?";
		String[] args = { a.getUserId(),a.getSessionId(),a.getTicketId()+""};
		return change(sql, args);
	}

	public Ticket SelectById(String id) {
		// TODO Auto-generated method stub
		Ticket a = null;
		String sql = "select * from ticket where ticketId=?";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				a = new Ticket();
				a.setTicketId(rs.getInt("ticketId"));
				a.setUserId(rs.getString("userId"));
				a.setSessionId(rs.getString("sessionId"));;
				a.setSeatRow(rs.getInt("seatRow"));
				a.setSeatCol(rs.getInt("seatCol"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	
	/**
	 * 获取所有数据
	 */
	public List<Ticket> selectAll() {
		// TODO Auto-generated method stub
		List<Ticket> list = new ArrayList<Ticket>();
		Ticket a = null;
		String sql = "select * from ticket";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Ticket();
				a.setTicketId(rs.getInt("ticketId"));
				a.setUserId(rs.getString("userId"));
				a.setSessionId(rs.getString("sessionId"));;
				a.setSeatRow(rs.getInt("seatRow"));
				a.setSeatCol(rs.getInt("seatCol"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	

	/**
	 * 根据关键词进行查询
	 */
	public List<Ticket> Select(String keyword) {
		// TODO Auto-generated method stub
		List<Ticket> list = new ArrayList<Ticket>();
		Ticket a = null;
		String sql = "select * from ticket where ticketId like '%" + keyword
				+ "%' or movieId like '%" + keyword
				+ "%' or userId like '%" + keyword + "%'";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Ticket();
				a.setTicketId(rs.getInt("ticketId"));
				a.setUserId(rs.getString("userId"));
				a.setSessionId(rs.getString("sessionId"));;
				a.setSeatRow(rs.getInt("seatRow"));
				a.setSeatCol(rs.getInt("seatCol"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				
				
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据条件进行查询 查询所有用户在该场次购买的
	 */
	public List<Ticket> selectByWhere(String where) {
		List<Ticket> list = new ArrayList<Ticket>();
		Ticket a = null;
		String sql = "select * from ticket where sessionId=?"; // where字句
		
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			pstmt.setString(1, where);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Ticket();
				a.setTicketId(rs.getInt("ticketId"));
				a.setUserId(rs.getString("userId"));
				a.setSessionId(rs.getString("sessionId"));;
				a.setSeatRow(rs.getInt("seatRow"));
				a.setSeatCol(rs.getInt("seatCol"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 查询指定用户所有电影票信息
	 */
	public List<Map<String, Object>> selectByUser(String userId) {
		List<Map<String, Object>> list = null;
		ModelConvert model=new ModelConvert();
		String sql = "select movieName,time,seatRow,seatCol,hallName,duration from ticket,movie,session,hall"
				+ " where userId=? and movie.movieId=ticket.movieId and"
				+ " session.hallId=ticket.hallId and `session`.movieId=ticket.movieId and"
				+ "  hall.hallId=ticket.hallId"; // where字句

		Connection con = getCon();
		try {
			PreparedStatement ps = (PreparedStatement) con.prepareStatement(sql);
			ps.setString(1, userId);
            ResultSet rs = ps.executeQuery();
            list  = model.convertList(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return list;
	}
	
	/**
	 * 指定用户在指定场次购买过的电影票
	 * @param sessionId
	 * @param userId
	 * @return
	 */
	public List<Ticket> selectBuyedTickets(String sessionId,String userId) {
		List<Ticket> list = new ArrayList<Ticket>();
		Ticket a = null;
		String sql = "select * from ticket where sessionId=? and userId=?"; // where字句
		
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			pstmt.setString(1, sessionId);
			pstmt.setString(2, userId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Ticket();
				a.setTicketId(rs.getInt("ticketId"));
				a.setUserId(rs.getString("userId"));
				a.setSessionId(rs.getString("sessionId"));;
				a.setSeatRow(rs.getInt("seatRow"));
				a.setSeatCol(rs.getInt("seatCol"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));				
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取所有的收入
	 */
	public List<MsgStatistical> selectByStatWhere() {
		// TODO Auto-generated method stub
		MsgStatistical m = null;
		List<MsgStatistical> list = new ArrayList<MsgStatistical>();
		String sql = "select \r\n" + 
				"	date_format(s.time, '%Y-%m-%d' ) as \"time\",\r\n" + 
				"	SUM(s.price) as \"totalPrice\"\r\n" + 
				"from ticket as t\r\n" + 
				"LEFT JOIN session as s ON t.sessionId = s.sessionId\r\n" + 
				"GROUP BY date_format(s.time, '%Y-%m-%d' )\r\n" + 
				"ORDER BY s.time \r\n" + 
				"";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				m.setCount(rs.getString("totalPrice"));
				m.setTime(rs.getString("time"));
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 热映影片 票房占比
	 */
	public List<MsgStatistical> selectByStatAccount() {
		// TODO Auto-generated method stub
		MsgStatistical m = null;
		List<MsgStatistical> list = new ArrayList<MsgStatistical>();
		String sql = "SELECT\r\n" + 
				"	s.movieId AS movieId,\r\n" + 
				"	m.movieName AS movieName,\r\n" + 
				"	SUM(s.price) AS totalPrice,\r\n" + 
				"	(\r\n" + 
				"		SELECT\r\n" + 
				"			SUM(se.price)\r\n" + 
				"		FROM\r\n" + 
				"			ticket AS c\r\n" + 
				"		LEFT JOIN SESSION AS se ON c.sessionId = se.sessionId\r\n" + 
				"		LEFT JOIN movie AS mo ON c.movieId = mo.movieId\r\n" + 
				"		WHERE\r\n" + 
				"			mo.movieStat = \"01\"\r\n" + 
				"	) AS allIncome\r\n" + 
				"FROM\r\n" + 
				"	ticket AS t\r\n" + 
				"LEFT JOIN SESSION AS s ON t.sessionId = s.sessionId\r\n" + 
				"LEFT JOIN movie AS m ON t.movieId = m.movieId\r\n" + 
				"WHERE\r\n" + 
				"	m.movieStat = \"01\"\r\n" + 
				"GROUP BY\r\n" + 
				"	s.movieId";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				m.setCount(rs.getString("totalPrice")); // 单个热映影片的总收入
				m.setTime(rs.getString("allIncome")); // 全部影片的总收入
				m.setMovieId(rs.getString("movieId")); // 电影id
				m.setMovieName(rs.getString("movieName")); // 电影名称
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
